package com.example.dao;

import com.example.model.Salary;
import com.example.util.DBUtil;

import java.sql.*;

import java.util.ArrayList;
import java.util.List;

public class SalaryDAO {
    // 添加工资记录
    public void addSalary(Salary salary) throws SQLException {
        String sql = "INSERT INTO salaries (emp_id, base_salary, bonus, deduction) VALUES (?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, salary.getEmpId());
            stmt.setDouble(2, salary.getBaseSalary());
            stmt.setDouble(3, salary.getBonus());
            stmt.setDouble(4, salary.getDeduction());
            stmt.executeUpdate();
        }
    }

    // 获取所有工资记录 (带分页)
    public List<Salary> getAllSalaries(int page, int recordsPerPage) throws SQLException {
        List<Salary> salaries = new ArrayList<>();
        int start = (page - 1) * recordsPerPage;


        String sql = "SELECT s.*, e.name AS emp_name FROM salaries s " +
                "JOIN employees e ON s.emp_id = e.id " +
                "LIMIT ?, ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, start );
            stmt.setInt(2,recordsPerPage);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Salary salary = new Salary();
                    salary.setSalaryId(rs.getInt("salary_id"));
                    salary.setEmpId(rs.getInt("emp_id"));
                    salary.setEmpName(rs.getString("emp_name"));
                    salary.setBaseSalary(rs.getDouble("base_salary"));
                    salary.setBonus(rs.getDouble("bonus"));
                    salary.setDeduction(rs.getDouble("deduction"));
                    salaries.add(salary);
                }
            }
        }
        return salaries;
    }

    // 获取工资记录总数
    public int getTotalSalaries() throws SQLException {
        String sql = "SELECT COUNT(*) AS total FROM salaries";
        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                return rs.getInt("total");
            }
            return 0;
        }
    }
    // 更新工资记录
    public void updateSalary(Salary salary) throws SQLException {
        String sql = "UPDATE salaries SET emp_id=?, base_salary=?, bonus=?, deduction=?, WHERE salary_id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, salary.getEmpId());
            stmt.setDouble(2, salary.getBaseSalary());
            stmt.setDouble(3, salary.getBonus());
            stmt.setDouble(4, salary.getDeduction());
            stmt.setInt(5, salary.getSalaryId());
            stmt.executeUpdate();
        }
    }

    // 删除工资记录
    public void deleteSalary(int salaryId) throws SQLException {
        String sql = "DELETE FROM salaries WHERE salary_id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, salaryId);
            stmt.executeUpdate();
        }
    }

    // 根据ID获取工资记录
    public Salary getSalaryById(int salaryId) throws SQLException {
        String sql = "SELECT s.*, e.name AS emp_name FROM salaries s " +
                "JOIN employees e ON s.emp_id = e.id " +
                "WHERE s.salary_id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, salaryId);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    Salary salary = new Salary();
                    salary.setSalaryId(rs.getInt("salary_id"));
                    salary.setEmpId(rs.getInt("emp_id"));
                    salary.setEmpName(rs.getString("emp_name"));
                    salary.setBaseSalary(rs.getDouble("base_salary"));
                    salary.setBonus(rs.getDouble("bonus"));
                    salary.setDeduction(rs.getDouble("deduction"));
                    return salary;
                }
            }
        }
        return null;
    }
}
